﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Wild2.Models.User
{
    public class User
    {
        public static UserData GetUserData(int userID)
        {
            UserData userData = new UserData();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [ID], [Nick], [Address], [Birth], [Rating], [CommentsCount], [TopicsCount], 
                                                    [RegDate], [LastOnlineDate], [isMale] FROM [Users] WHERE ([ID] = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters["@UID"].Value = userID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    userData.ID = Convert.ToInt32(reader["id"]);
                    userData.Nick = reader["Nick"].ToString();
                    userData.Address = reader["Address"].ToString();
                    if (reader["Birth"] != DBNull.Value) userData.Birth =  Convert.ToDateTime(reader["Birth"]);
                    userData.Rating = Convert.ToInt32(reader["Rating"]);
                    userData.CountComments = Convert.ToInt32(reader["CommentsCount"]);
                    userData.CountTopics = Convert.ToInt32(reader["TopicsCount"]);
                    userData.RegDate = Convert.ToDateTime(reader["RegDate"]);
                    userData.LastOnlineDate = Convert.ToDateTime(reader["LastOnlineDate"]);
                    if (reader["isMale"] != DBNull.Value) userData.isMale = Convert.ToBoolean(reader["isMale"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return userData;
        }

        public static List<UserData> GetUsersData(int p)
        {
            List<UserData> usersData = new List<UserData>();
            int ElOnPage = 15;
            if (ConfigurationManager.AppSettings["elonpage"] != null) { ElOnPage = Int32.Parse(ConfigurationManager.AppSettings["elonpage"]) * 3; }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT        [ID], [Nick], [Address], [Birth], [Rating], [CommentsCount], [TopicsCount], 
                                                    [RegDate], [LastOnlineDate], [isMale]  FROM            (
                                                    SELECT        TOP (100) PERCENT  ROW_NUMBER() OVER (ORDER BY [Users].[Rating] DESC) AS num, 
                                                    [ID], [Nick], [Address], [Birth], [Rating], [CommentsCount], [TopicsCount], 
                                                    [RegDate], [LastOnlineDate], [isMale] FROM [Users]) T
                                                WHERE        (num > @KolZap1) AND (num <= @KolZap2)", conn);
                cmd.Parameters.Add("@KolZap1", SqlDbType.Int);
                cmd.Parameters.Add("@KolZap2", SqlDbType.Int);
                cmd.Parameters["@KolZap1"].Value = ((p - 1) * ElOnPage);
                cmd.Parameters["@KolZap2"].Value = (((p - 1) * ElOnPage) + ElOnPage);
                cmd.Connection.Open(); 
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    UserData userData = new UserData();
                    userData.ID = Convert.ToInt32(reader["id"]);
                    userData.Nick = reader["Nick"].ToString();
                    userData.Address = reader["Address"].ToString();
                    if (reader["Birth"] != DBNull.Value) userData.Birth = Convert.ToDateTime(reader["Birth"]);
                    userData.Rating = Convert.ToInt32(reader["Rating"]);
                    userData.CountComments = Convert.ToInt32(reader["CommentsCount"]);
                    userData.CountTopics = Convert.ToInt32(reader["TopicsCount"]);
                    userData.RegDate = Convert.ToDateTime(reader["RegDate"]);
                    userData.LastOnlineDate = Convert.ToDateTime(reader["LastOnlineDate"]);
                    if (reader["isMale"] != DBNull.Value) userData.isMale = Convert.ToBoolean(reader["isMale"]);
                    usersData.Add(userData);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return usersData;
        }

        public static List<UserData> GetUsersData(int p, string nick)
        {
            if (nick.Length > 20) nick = nick.Substring(0, 20);
            nick = nick.Replace(@"%", @"\%");
            nick = nick.Replace(@"_", @"\_");
            nick = nick.Replace(@"!", @"\!");

            List<UserData> usersData = new List<UserData>();
            int ElOnPage = 15;
            if (ConfigurationManager.AppSettings["elonpage"] != null) { ElOnPage = Int32.Parse(ConfigurationManager.AppSettings["elonpage"]) * 3; }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT        [ID], [Nick], [Address], [Birth], [Rating], [CommentsCount], [TopicsCount], 
                                                    [RegDate], [LastOnlineDate], [isMale]  FROM            (
                                                    SELECT        TOP (100) PERCENT  ROW_NUMBER() OVER (ORDER BY [Users].[Rating] DESC) AS num, 
                                                    [ID], [Nick], [Address], [Birth], [Rating], [CommentsCount], [TopicsCount], 
                                                    [RegDate], [LastOnlineDate], [isMale] FROM [Users] WHERE ([Nick] LIKE @Nick ESCAPE '\')) T
                                                WHERE        (num > @KolZap1) AND (num <= @KolZap2)", conn);
                cmd.Parameters.Add("@Nick", SqlDbType.NVarChar, 20).Value = "%" + nick + "%";
                cmd.Parameters.Add("@KolZap1", SqlDbType.Int);
                cmd.Parameters.Add("@KolZap2", SqlDbType.Int);
                cmd.Parameters["@KolZap1"].Value = ((p - 1) * ElOnPage);
                cmd.Parameters["@KolZap2"].Value = (((p - 1) * ElOnPage) + ElOnPage);
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    UserData userData = new UserData();
                    userData.ID = Convert.ToInt32(reader["id"]);
                    userData.Nick = reader["Nick"].ToString();
                    userData.Address = reader["Address"].ToString();
                    if (reader["Birth"] != DBNull.Value) userData.Birth = Convert.ToDateTime(reader["Birth"]);
                    userData.Rating = Convert.ToInt32(reader["Rating"]);
                    userData.CountComments = Convert.ToInt32(reader["CommentsCount"]);
                    userData.CountTopics = Convert.ToInt32(reader["TopicsCount"]);
                    userData.RegDate = Convert.ToDateTime(reader["RegDate"]);
                    userData.LastOnlineDate = Convert.ToDateTime(reader["LastOnlineDate"]);
                    if (reader["isMale"] != DBNull.Value) userData.isMale = Convert.ToBoolean(reader["isMale"]);
                    usersData.Add(userData);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return usersData;
        }

        public static List<UserData> GetSubscribersDataToHim(Int32 userID)
        {
            List<UserData> usersData = new List<UserData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT TOP(500) [ID], [Nick], [Address], [Birth], [Rating], [CommentsCount], [TopicsCount], 
                                                  [RegDate], [LastOnlineDate], [isMale] FROM [Users] 
                                                WHERE (ID IN (SELECT [KTO] FROM Subscribes WHERE [Kogo] = @UID))
                                                ORDER BY [Rating] DESC", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    UserData userData = new UserData();
                    userData.ID = Convert.ToInt32(reader["id"]);
                    userData.Nick = reader["Nick"].ToString();
                    userData.Address = reader["Address"].ToString();
                    if (reader["Birth"] != DBNull.Value) userData.Birth = Convert.ToDateTime(reader["Birth"]);
                    userData.Rating = Convert.ToInt32(reader["Rating"]);
                    userData.CountComments = Convert.ToInt32(reader["CommentsCount"]);
                    userData.CountTopics = Convert.ToInt32(reader["TopicsCount"]);
                    userData.RegDate = Convert.ToDateTime(reader["RegDate"]);
                    userData.LastOnlineDate = Convert.ToDateTime(reader["LastOnlineDate"]);
                    if (reader["isMale"] != DBNull.Value) userData.isMale = Convert.ToBoolean(reader["isMale"]);
                    usersData.Add(userData);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return usersData;
        }

        public static List<UserData> GetSubscribersDataHe(Int32 userID)
        {
            List<UserData> usersData = new List<UserData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT TOP(500) [ID], [Nick], [Address], [Birth], [Rating], [CommentsCount], [TopicsCount], 
                                                  [RegDate], [LastOnlineDate], [isMale] FROM [Users] 
                                                WHERE (ID IN (SELECT [Kogo] FROM Subscribes WHERE [Kto] = @UID))
                                                ORDER BY [Rating] DESC", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    UserData userData = new UserData();
                    userData.ID = Convert.ToInt32(reader["id"]);
                    userData.Nick = reader["Nick"].ToString();
                    userData.Address = reader["Address"].ToString();
                    if (reader["Birth"] != DBNull.Value) userData.Birth = Convert.ToDateTime(reader["Birth"]);
                    userData.Rating = Convert.ToInt32(reader["Rating"]);
                    userData.CountComments = Convert.ToInt32(reader["CommentsCount"]);
                    userData.CountTopics = Convert.ToInt32(reader["TopicsCount"]);
                    userData.RegDate = Convert.ToDateTime(reader["RegDate"]);
                    userData.LastOnlineDate = Convert.ToDateTime(reader["LastOnlineDate"]);
                    if (reader["isMale"] != DBNull.Value) userData.isMale = Convert.ToBoolean(reader["isMale"]);
                    usersData.Add(userData);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return usersData;
        }

        public static void SetUserAddress(int userID, string address)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE [Users] SET [Address] = @Address WHERE ([ID] = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 200);
                cmd.Parameters["@UID"].Value = userID;
                cmd.Parameters["@Address"].Value = address;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
        }

        public static int GetUserRating(int userID)
        {
            int rating = 0;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [Rating] FROM [Users] WHERE ([ID] = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters["@UID"].Value = userID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    rating = Convert.ToInt32(reader["rating"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return rating;
        }

        public static int GetCountUsers()
        {
            int usersCount = 0;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT COUNT(ID) as CNT FROM [Users]", conn);
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    usersCount = Convert.ToInt32(reader["CNT"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return usersCount;
        }

        public static int GetCountUsers(string nick)
        {
            if (nick.Length > 20) nick = nick.Substring(0, 20);
            nick = nick.Replace(@"%", @"\%");
            nick = nick.Replace(@"_", @"\_");
            nick = nick.Replace(@"!", @"\!");

            int usersCount = 0;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT COUNT(ID) as CNT FROM [Users] WHERE ([Nick] LIKE @Nick ESCAPE '\')", conn);
                cmd.Parameters.Add("@Nick", SqlDbType.NVarChar, 20).Value = "%" + nick + "%";
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    usersCount = Convert.ToInt32(reader["CNT"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return usersCount;
        }

        public static bool VoteUserPlus(int ktoID, int userID)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"DELETE FROM [UserVotes] WHERE ([UserID] = @UID) AND ([Kto] = @Kto)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Parameters.Add("@Kto", SqlDbType.Int).Value = ktoID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [UserVotes]([Kto], [UserID], [Val]) VALUES(@Kto, @UID, 1)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Parameters.Add("@Kto", SqlDbType.Int).Value = ktoID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE [Users] SET Rating = (SELECT SUM(Val) as S FROM UserVotes WHERE ([UserID] = @UID)) WHERE (ID = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }

            return true;
        }

        public static bool VoteUserMinus(int ktoID, int userID)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"DELETE FROM [UserVotes] WHERE ([UserID] = @UID) AND ([Kto] = @Kto)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Parameters.Add("@Kto", SqlDbType.Int).Value = ktoID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [UserVotes]([Kto], [UserID], [Val]) VALUES(@Kto, @UID, -1)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Parameters.Add("@Kto", SqlDbType.Int).Value = ktoID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE [Users] SET Rating = (SELECT SUM(Val) as S FROM UserVotes WHERE ([UserID] = @UID)) WHERE (ID = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            return true;
        }


        public static List<Wild2.Models.Votes.VoteUserData> GetVotesList(int userID)
        {
            List<Wild2.Models.Votes.VoteUserData> votes = new List<Votes.VoteUserData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [Kto], [Nick], [Val] FROM [UserVotes] INNER JOIN [Users] ON [UserVotes].[Kto] = [Users].[ID] WHERE ([UserID] = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    votes.Add(new Votes.VoteUserData() { UserID = userID, KtoID = Convert.ToInt32(reader["kto"]), UserNick = reader["Nick"].ToString(), Vote = Convert.ToInt32(reader["val"]) });
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return votes;
        }

        public static void SetProfileData(Wild2.Models.Pages.User.Profile user)//int userID, String day, String month, String year, String email, String male)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE [Users] SET [isMale] =  @Male, [Birth] =  @Birth, [email] = @Email WHERE ([ID] = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters["@UID"].Value = user.id;

                cmd.Parameters.Add("@Male", SqlDbType.Bit);
                if (user.isMale.HasValue) cmd.Parameters["@Male"].Value = user.isMale.Value;
                else cmd.Parameters["@Male"].Value = DBNull.Value;
                
                cmd.Parameters.Add("@Birth", SqlDbType.DateTime);
                if (user.birth.HasValue) cmd.Parameters["@Birth"].Value = user.birth.Value;
                else cmd.Parameters["@Birth"].Value = DBNull.Value;
                
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100);
                if (!string.IsNullOrEmpty(user.email)) cmd.Parameters["@Email"].Value = user.email;
                else cmd.Parameters["@Email"].Value = DBNull.Value;

                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
        }

        public static Wild2.Models.Pages.User.Profile GetProfileData(int userID)//int userID, String day, String month, String year, String email, String male)
        {
            Wild2.Models.Pages.User.Profile profile = new Pages.User.Profile();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [id], [isMale], [birth], [PrivateKey], [email] FROM [Users]  WHERE ([ID] = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters["@UID"].Value = userID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    profile.id = Convert.ToInt32(reader["id"]);
                    if (reader["isMale"] != DBNull.Value) profile.isMale = Convert.ToBoolean(reader["isMale"]);
                    if (reader["birth"] != DBNull.Value) profile.birth = Convert.ToDateTime(reader["birth"]);
                    profile.key = reader["PrivateKey"].ToString();
                    if (reader["email"] != DBNull.Value) profile.email = reader["email"].ToString();
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return profile;
        }
    }
}